set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 400;
set hive.exec.max.dynamic.partitions.pernode = 200;
with bagging_1_7all as (
    select waybill_no     --运单号
         , package_code   --包号
         , scan_time      --建包扫描时间
         , scan_site_code --建包网点
         , flight_num     --芯片号
         , good_type      --物品类型1是普通类型 2是文件类型
    from jms_dwd.dwd_tab_barscan_bagging_base_hi
    where substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -6) and date_add('{{ execution_date | cst_ds }}', 1)
)
   , bagging_137union as
    (--合并T-1和T-3和T-7的包数据
        select waybill_no     --运单号
             , package_code   --包号
             , scan_time      --建包扫描时间
             , scan_site_code --建包网点
             , flight_num     --芯片号
             , good_type      --物品类型1是普通类型 2是文件类型
             , '{{ execution_date | cst_ds }}' as day_time
        from bagging_1_7all
        where scan_time >= concat('{{ execution_date | cst_ds }}', ' 06:00:00')
          and scan_time < concat(date_add('{{ execution_date | cst_ds }}', 1), ' 06:00:00')

        union all

        select waybill_no
             , package_code
             , scan_time
             , scan_site_code
             , flight_num --芯片号
             , good_type  --物品类型1是普通类型 2是文件类型
             , date_add('{{ execution_date | cst_ds }}', -2) as day_time
        from bagging_1_7all
        where scan_time >= concat(date_add('{{ execution_date | cst_ds }}', -2), ' 06:00:00')
          and scan_time < concat(date_add('{{ execution_date | cst_ds }}', -1), ' 06:00:00')
        union all

        select waybill_no
             , package_code
             , scan_time
             , scan_site_code
             , flight_num --芯片号
             , good_type  --物品类型1是普通类型 2是文件类型
             , date_add('{{ execution_date | cst_ds }}', -6) as day_time
        from bagging_1_7all
        where scan_time >= concat(date_add('{{ execution_date | cst_ds }}', -6), ' 06:00:00')
          and scan_time < concat(date_add('{{ execution_date | cst_ds }}', -5), ' 06:00:00')
    )
   , bagging_137union_waydis as
    (--包
        select waybill_no
             , max(flight_num)     as flight_num
             , max(scan_time)      as scan_time
             , max(day_time)       as day_time
             , max(scan_site_code) as scan_site_code
        from bagging_137union a
        where (waybill_no like 'B%' or waybill_no like '4%')
        group by waybill_no
    )
   , bagging_137union_pagdis as
    (
        select package_code                        --包号
             , count(waybill_no) as waybill_no_num --包内件
        from (
                 select waybill_no     --运单号
                      , package_code   --包号
                      , scan_time      --建包扫描时间
                      , scan_site_code --建包网点
                      , row_number()
                         over (partition by package_code ,waybill_no,scan_site_code order by scan_time desc) rn
                 from bagging_1_7all
                 where package_code is not null
             ) a
        where a.rn = 1
        group by package_code
    )
   , package_list as
    (
        select package_number --包号
             , center_code    --拆包目的地编码
             , center_name    --拆包目的地名称
        from (
                 select package_number --包号
                      , center_code    --拆包目的地编码
                      , center_name    --拆包目的地名称
                      , row_number() over (partition by package_number order by used_time desc ) as rn
                 from jms_dwd.dwd_electronic_package_list_base_hi
                 where substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -60) and date_add('{{ execution_date | cst_ds }}', 1)
                   and package_number is not null
             ) a
        where rn = 1
    )
   , arrival_base as
    (
        select waybill_no as package_code --到件包号
             , scan_site_code             --到件扫描网点
             , scan_time                  --到件扫描时间
        from (
                 select waybill_no     --到件包号
                      , scan_site_code --找到拆包目的地的到件扫描网点
                      , scan_time      --到件扫描时间
                      , row_number() over (partition by waybill_no order by scan_time desc) as rn
                 from jms_dwd.dwd_tab_barscan_arrival_base_hi
                 where substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -6) and date_add('{{ execution_date | cst_ds }}', 1)
                   and (waybill_no like 'B%' or waybill_no like '4%')
                   and waybill_no is not null
             ) a
        where a.rn = 1
    )
   , send_hk as
    (
        select waybill_no as package_code
        from jms_dwd.dwd_tab_barscan_send_base_hi
        where transport_type = '航空运输'
          and substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -6) and date_add('{{ execution_date | cst_ds }}', 1)
          and (waybill_no like 'B%' or waybill_no like '4%')
          and waybill_no is not null
        group by waybill_no
    )--1441
   , other_base as
    (
        select waybill_no as package_code --拆包包号
             , scan_time                  --拆包扫描时间
             , scan_site_code             --找到拆包目的地
        from (
                 select waybill_no --拆包包号
                      , scan_time  --拆包扫描时间
                      , scan_site_code
                      , row_number() over (partition by waybill_no order by scan_time) as rn
                 from jms_dwd.dwd_tab_barscan_other_base_hi
                 where substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -6) and date_add('{{ execution_date | cst_ds }}', 1)
                   and (waybill_no like 'B%' or waybill_no like '4%')
                   and scan_type = '拆包扫描'
                   and waybill_no is not null
             ) a
        where rn = 1
    )
   , network_ma as
    (
        select code         --网点编码
             , name         --网点名称
             , manage_code  --管理大区编码
             , manage_name  --管理大区名称
             , agent_code   --代理区编码
             , agent_name   --代理区名称
             , network_type --网点类型
        from jms_dim.dim_network_whole_massage
    )
   , recycle_bag as
    (
        select site_code
             , site_name
             , create_time
        from jms_dim.dim_tab_report_recycleoption_base --流向违规
    )
   , second_package as
    (
        select package_number --包号
             , center_code    --拆包网点编码
             , center_name    --拆包网点名称
        from (
                 select package_number --包号
                      , center_code    --拆包网点编码
                      , center_name    --拆包网点名称
                      , row_number() over (partition by package_number order by create_time desc ) as rn
                 from jms_dwd.dwd_second_package_list_base_hi
                 where substr(dt, 1, 10) between date_add('{{ execution_date | cst_ds }}', -60) and date_add('{{ execution_date | cst_ds }}', 1)
             ) a
        where a.rn = 1
    )
   , bagging_goodtype as
    (
        select package_code --包号
             , good_type
        from bagging_1_7all
        where package_code is not null
        group by package_code --包号
               , good_type
    )
insert
overwrite
table
jms_dm.dm_tab_loopbag_buildbag_detail_dt
select waydis.waybill_no                                                                 as package_code       --包号
     , waydis.scan_time                                                                  as scan_time          --建包扫描时间
     , waydis.scan_site_code                                                             as site_code          --建包网点
     , pagdis.waybill_no_num                                                             as package_qty        --包内件
     , waydis.flight_num                                                                 as chip_no            --芯片号
     , nvl(packlist.center_code, sec.center_code)                                        as open_code          --拆包目的地编码
     , nvl(packlist.center_name, sec.center_name)                                        as open_site          --拆包目的地名称
     , arrbase.scan_site_code                                                            as arr_scan_site      --到件扫描网点
     , arrbase.scan_time                                                                 as arrive_time        --到件扫描时间
     , otherbase.scan_time                                                               as open_time          --拆包扫描时间
     , networkma.name                                                                    as site_name          --建包对应网点名称
     , networkma.network_type                                                            as network_type       --网点类型
     , networkma.manage_code                                                             as region_code        --建包对应网点大区编码
     , networkma.manage_name                                                             as region_name        --建包对应网点大区名称
     , networkma.agent_code                                                              as proxy_area_code    --建包对应网点代理区编码
     , networkma.agent_name                                                              as proxy_area_name    --建包对应网点代理区名称
     , case when substr(waydis.waybill_no, 1, 2) in ('B4','B7', 'B8', 'B9') then 1 else 0 end as transfer_type_desc --建包类型
     , case
           when waydis.waybill_no like 'B%' then 1
           when waydis.waybill_no like '4%' then 0
           else 2 end                                                                    as package_source     --建包来源 1代表JT包 0代表BS包 2代表其他
     , case when waydis.flight_num is not null then 1 else 0 end                         as exact_flag         --循环袋标识:1代表循环袋,0代表非循环袋
--      , case when recylebag.site_code is not null then 1 else 0 end                       as is_break           --流向违规标识:1违规,0代表不违规
     , 0                                                                                 as is_break           --流向违规标识:1违规,0代表不违规
     , waydis.day_time                                                                   as day_time           --日期
     , bag_goodtype.good_type                                                                                  --物品类型1是普通类型 2是文件类型
     , waydis.day_time                                                                   as dt                 --分区
from bagging_137union_waydis waydis
         left anti
         join send_hk sendhk
              on waydis.waybill_no = sendhk.package_code
         left join bagging_137union_pagdis pagdis
                   on pagdis.package_code = waydis.waybill_no
         left join package_list packlist
                   on packlist.package_number = waydis.waybill_no
         left join second_package sec
                   on sec.package_number = waydis.waybill_no
         left join arrival_base arrbase
                   on waydis.waybill_no = arrbase.package_code
                       and nvl(packlist.center_code, sec.center_code) = arrbase.scan_site_code
         left join other_base otherbase
                   on otherbase.package_code = waydis.waybill_no
                       and otherbase.scan_site_code = nvl(packlist.center_code, sec.center_code)
         left join network_ma networkma
                   on networkma.code = waydis.scan_site_code
--          left join recycle_bag recylebag
--                    on packlist.center_code = recylebag.site_code
         left join bagging_goodtype bag_goodtype
                   on bag_goodtype.package_code = waydis.waybill_no

 distribute by pmod(hash(rand()), 2)
;